﻿using DataAccess.Enums;
using DataAccess.FrameWork;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace DataAccess.Dal
{
    public class DBHelper
    {
        /// <summary>
        /// 查询集合，带分页
        /// </summary>
        /// <param name="item">框架实体</param>
        /// <param name="isAutoParam">是否自动匹配参数</param>
        /// <param name="dp">分页对象</param>
        /// <returns>对象集合</returns>
        public static List<To> SelectList<To>(FrameWorkItem item, DBType dbType, bool isAutoParam = false) where To : new()
        {
            try
            {
                var sql = DalAid.CreatePageQuerySql(item.Sql, null, dbType);
                var param = item.SqlParam;
                if (isAutoParam)
                {
                    var where = GetWhere(item.SqlParam);
                    sql += where.Item1;
                    param = where.Item2;
                }
                using (var connection = GetDbConnection(item.ConnectionString, dbType))
                {
                    using (var cmd = GetDbCommand(sql, connection, param, dbType))
                    {
                        using (IDataReader dr = cmd.ExecuteReader())
                        {
                            List<To> list = new List<To>();

                            var builders = DynamicBuilderEntity<To>.CreateBuilder(dr);
                            while (dr != null && dr.Read())
                            {
                                To tempT = new To();
                                tempT = builders.Build(dr);
                                list.Add(tempT);
                            }
                            return list;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private static DbConnection GetDbConnection(string dbConnectionString, DBType dbType)
        {
            DbConnection connection = null;
            if (dbType == DBType.MYSQL)
            {
                connection = new MySqlConnection(dbConnectionString);
            }
            else if (dbType == DBType.SQLSERVER)
            {
                connection = new SqlConnection(dbConnectionString);
            }
            return connection;
        }

        private static IDbCommand GetDbCommand(string sql, DbConnection connection, Dictionary<string, object> cmdParms, DBType dbType)
        {
            if (dbType == DBType.MYSQL)
            {
                MySqlConnection mysqlConnection = connection as MySqlConnection;
                var cmd = new MySqlCommand(sql, mysqlConnection);
                if (cmdParms != null)
                {
                    foreach (var item in cmdParms)
                    {
                        cmd.Parameters.AddWithValue(item.Key, item.Value);
                    }
                }
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                return cmd;
            }
            else
            {
                SqlConnection con = connection as SqlConnection;
                var cmd = new SqlCommand(sql, con);
                if (cmdParms != null)
                {
                    foreach (var item in cmdParms)
                    {
                        cmd.Parameters.AddWithValue(item.Key, item.Value);
                    }
                }
                return cmd;
            }
        }
        private static Tuple<string, Dictionary<string, object>> GetWhere(Dictionary<string, object> param)
        {
            var setStr = string.Empty;
            var paramDic = new Dictionary<string, object>();
            if (param != null && param.Count > 0)
            {
                setStr += " where 1=1 ";
                int index = 0;
                foreach (var paramItem in param)
                {
                    var par = GetCondition(paramItem.Key);
                    var paramName = "@Param" + ++index;
                    setStr += string.Format(" and {0} {1} {2}", par.Item2, par.Item1, paramName);
                    paramDic.Add(paramName, paramItem.Value);
                }
            }
            setStr = setStr.Trim(',');
            return new Tuple<string, Dictionary<string, object>>(setStr, paramDic);
        }

        /// <summary>
        /// 获取比较符
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        private static Tuple<string, string> GetCondition(string key)
        {
            var result = "=";
            var name = key;
            if (key[key.Length - 2] < 'A')
            {
                name = key.Substring(0, key.Length - 2);
                result = key.Substring(key.Length - 2);
            }
            else if (key[key.Length - 1] < 'A')
            {
                name = key.Substring(0, key.Length - 1);
                result = key.Substring(key.Length - 1);
            }
            return new Tuple<string, string>(result, name);
        }
    }
}
